package dyyx.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.lang3.StringUtils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import dyyx.rowbuild.LongBuilder;
import dyyx.rowbuild.RowBuilder;

public abstract class JdbcUtil {

	// DataSource DruidDataSource
	private static DataSource dataSource = null;

	static {

		try {
			DataSource dstmp = DruidDataSourceFactory.createDataSource(ConfigUtil.getConfig());
			dataSource = dstmp;
		} catch (Throwable e) {
			throw new RuntimeException("create data source error", e);
		}

	}

	public static Connection getConn() throws SQLException {
		return dataSource.getConnection();
	}


	
	public static void close(AutoCloseable obj){
		if(obj==null){
			return;
		}
		try {
			obj.close();
		} catch (Throwable e) {
			// ignore
		}
	}


	
	
	public static List<Long> queryLongList(String sql, Object[] params,int maxRows)throws SQLException {
		if(maxRows<=0){
			maxRows = 100;
		}
		
		List list =  query(sql, params, new LongBuilder(),maxRows);
		if(list==null || list.isEmpty()){
			return null;
		}
		List<Long> list2 = new ArrayList<Long>();
		for(Object item:list){
			list2.add((Long)item);
		}
		return list2;
	}
	
	public static Object queryOne(String sql, Object[] params, RowBuilder builder) throws SQLException {
		Connection cn = null;		
		try{
			cn = getConn();
			return queryOne(cn,sql,params,builder);
		} finally {
			close(cn);
		}
	}
	
	public static List query(String sql, Object[] params, RowBuilder builder,int maxRows) throws SQLException {
		Connection cn = null;		
		try{
			cn = getConn();
			return query(cn,sql,params,builder,maxRows);
		} finally {
			close(cn);
		}
	}

	public static List query(String sql, Object[] params, RowBuilder builder) throws SQLException {
		Connection cn = null;		
		try{
			cn = getConn();
			return query(cn,sql,params,builder,100);
		} finally {
			close(cn);
		}
	}
	
	public static int executeUpdate(String sql, Object[] params) throws SQLException {
		Connection cn = null;		
		try{
			cn = getConn();
			return executeUpdate(cn,sql,params);
		} finally {
			close(cn);
		}
	}
	
	public static Object queryOne(Connection cn,String sql, Object[] params, RowBuilder builder) throws SQLException {
		if(cn==null || builder==null){
			return null;
		}
		

		
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			pstmt = cn.prepareStatement(sql);
			if(params!=null && params.length>0){
				for(int i=0;i<params.length;i++){
					pstmt.setObject((i+1), params[i]);
				}
			}
			pstmt.setMaxRows(1);
			rs = pstmt.executeQuery();
			
			
			while(rs.next()){
				return builder.build(rs);
			}
			return null;
		} finally {
			close(rs);
			close(pstmt);
		}

	}
	
	public static int queryInt(Connection cn,String sql, Object[] params) throws SQLException {
		if(cn==null || StringUtils.isBlank(sql)){
			return 0;
		}
	
		
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			pstmt = cn.prepareStatement(sql);
			if(params!=null && params.length>0){
				for(int i=0;i<params.length;i++){
					pstmt.setObject((i+1), params[i]);
				}
			}
			pstmt.setMaxRows(1);
			rs = pstmt.executeQuery();					
			while(rs.next()){
				return rs.getInt(1);				
			}
			return 0;
		} finally {
			close(rs);
			close(pstmt);
		}
		
	}
	
	public static int queryInt(String sql, Object[] params) throws SQLException {
		if(StringUtils.isBlank(sql)){
			return 0;
		}
		Connection cn = null;		
		try{
			cn = getConn();
			return queryInt(cn,sql,params);
		} finally {
			close(cn);
		}
		
	}
	
	
	
	public static long queryLong(Connection cn,String sql, Object[] params) throws SQLException {
		if(cn==null || StringUtils.isBlank(sql)){
			return 0;
		}
		
	
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			pstmt = cn.prepareStatement(sql);
			if(params!=null && params.length>0){
				for(int i=0;i<params.length;i++){
					pstmt.setObject((i+1), params[i]);
				}
			}
			pstmt.setMaxRows(1);
			rs = pstmt.executeQuery();					
			while(rs.next()){
				return rs.getLong(1);				
			}
			return 0;
		} finally {
			close(rs);
			close(pstmt);
		}
		
	}
	
	public static long queryLong(String sql, Object[] params) throws SQLException {
		if(StringUtils.isBlank(sql)){
			return 0;
		}
		Connection cn = null;		
		try{
			cn = getConn();
			return queryLong(cn,sql,params);
		} finally {
			close(cn);
		}
		
	}


	
	public static Map<String,String> queryOneAsMap(Connection cn,String sql, Object[] params) throws SQLException {
		if(cn==null || StringUtils.isBlank(sql)){
			return null;
		}
		

		
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			pstmt = cn.prepareStatement(sql);
			if(params!=null && params.length>0){
				for(int i=0;i<params.length;i++){
					pstmt.setObject((i+1), params[i]);
				}
			}
			pstmt.setMaxRows(1);

			rs = pstmt.executeQuery();
			String[]cols = getColumns(rs);
			if(cols==null){
				return null;
			}
			int count = cols.length;
			if(count<=0){
				return null;
			}
			
					
			while(rs.next()){
				return getRowAsMap(rs,cols,count);				
			}
			return null;
		} finally {
			close(rs);
			close(pstmt);
		}

	}
	
	public static Map<String,String> queryOneAsMap(String sql, Object[] params) throws SQLException {
		Connection cn = null;		
		try{
			cn = getConn();
			return queryOneAsMap(cn,sql,params);
		} finally {
			close(cn);
		}
		
	}


	

	public static List query(Connection cn,String sql, Object[] params, RowBuilder builder,int maxRows) throws SQLException {
		if(cn==null || builder==null){
			return null;
		}
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			pstmt = cn.prepareStatement(sql);
			if(params!=null && params.length>0){
				for(int i=0;i<params.length;i++){
					pstmt.setObject((i+1), params[i]);
				}
			}
			if(maxRows>0){
			    pstmt.setMaxRows(maxRows);
			}
			
			rs = pstmt.executeQuery();
			List list = new ArrayList();
			Object obj = null;
			while(rs.next()){
				obj = builder.build(rs);
				list.add(obj);
			}
			return list;
		} finally {
			close(rs);
			close(pstmt);
		}
	}
	
	public static List<Map<String,String>> queryAsMap(Connection cn,String sql, Object[] params,int maxRows) throws SQLException {
		if(cn==null || StringUtils.isBlank(sql)){
			return null;
		}
		
		
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			pstmt = cn.prepareStatement(sql);
			if(params!=null && params.length>0){
				for(int i=0;i<params.length;i++){
					pstmt.setObject((i+1), params[i]);
				}
			}
			if(maxRows>0){
			    pstmt.setMaxRows(maxRows);
			}
			rs = pstmt.executeQuery();
			
			String[]cols = getColumns(rs);
			if(cols==null){
				return null;
			}
			int count = cols.length;
			if(count<=0){
				return null;
			}
			
			List<Map<String,String>> list = new ArrayList<Map<String,String>>();
			while(rs.next()){
				list.add(getRowAsMap(rs,cols,count));
			}
			return list;
		} finally {
			close(rs);
			close(pstmt);
		}
	}
	
	
	public static List<Map<String,String>> queryAsMap(String sql, Object[] params,int max) throws SQLException {
		if(max<=0){
			max=100;
		}
		
		Connection cn = null;		
		try{
			cn = getConn();
			return queryAsMap(cn,sql,params,max);
		} finally {
			close(cn);
		}	
		
	}

	
	public static List<Map<String,String>> queryAsMap(String sql, Object[] params) throws SQLException {
		Connection cn = null;		
		try{
			cn = getConn();
			return queryAsMap(cn,sql,params,100);
		} finally {
			close(cn);
		}	
	}
	
	public static int executeUpdate(Connection cn,String sql, Object[] params) throws SQLException {
		if(cn==null || StringUtils.isBlank(sql)){
			return -1;
		}
		
		
		PreparedStatement pstmt = null;

		try {
			pstmt = cn.prepareStatement(sql);
			if(params!=null && params.length>0){
				for(int i=0;i<params.length;i++){
					pstmt.setObject((i+1), params[i]);
				}
			}
			return pstmt.executeUpdate();
		} finally {
			close(pstmt);
		}
	}
	
	private static int executeUpdate0(Connection cn,String sql) throws SQLException {
		if(cn==null || StringUtils.isBlank(sql)){
			return -1;
		}
		Statement stmt = null;

		try {
			stmt = cn.createStatement();	
			return stmt.executeUpdate(sql);
		} finally {
			close(stmt);
		}
	}
	
	
	

	
	private static String[] getColumns(ResultSet rs) throws SQLException{
		ResultSetMetaData rsmd = rs.getMetaData();
		int count = rsmd.getColumnCount();
		if(count<=0){
			return null;
		}
		String[]cols = new String[count];
		String label = null;
		for(int i=0;i<count;i++){
			label = rsmd.getColumnLabel(i+1);
			label =label.toLowerCase();
			cols[i] = label;
		}	
		return cols;
	}
	
	private static Map<String,String> getRowAsMap(ResultSet rs,String[]cols,int count)throws SQLException{
		Map<String,String> row = new HashMap<String,String>();
		for(int i=0;i<count;i++){
			row.put(cols[i], rs.getString(i+1));
		}
		return row;
	}

	
}
